package cn.ctbu.xiaopeng.studentscoremanage.dao;


import cn.ctbu.xiaopeng.studentscoremanage.pojo.dto.StudentQueryDTO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.dto.TeacherQueryDTO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.entity.Teacher;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.StudentPageVO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.TeacherPageVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author xiaopeng
 * @version 1.0
 */
public interface TeacherRepository extends JpaRepository<Teacher, Integer> {

    /**
     * 通过将departmentId将教师表和院系表连接起来，查询所属院系信息
     * 使用 @Query 注解进行自定义查询，分页查询教师信息并返回
     *
     * @param
     * @param pageable
     * @return
     */
    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.TeacherPageVO(" +
            "t.id, " +
            "t.tno, " +
            "t.name, " +
            "t.sex, " +  // 从班级表获取班级名称
            "t.age, " +
            "t.qualifications, " +
            "t.departmentId, " +
            "d.department, " +  // 从部门表获取部门名称
            "t.password, " +
            "t.phone, " +
            "t.email) " +
            "FROM tb_teacher t " +
            "LEFT JOIN tb_department d ON t.departmentId = d.departmentId " + // 连接部门表
            "WHERE (:#{#teacherQueryDTO.name} IS NULL OR t.name LIKE %:#{#teacherQueryDTO.name}%) " +
            "AND (:#{#teacherQueryDTO.tno} IS NULL OR t.tno LIKE %:#{#teacherQueryDTO.tno}%) " +
            "AND (:#{#teacherQueryDTO.name} IS NULL OR t.name LIKE %:#{#teacherQueryDTO.name}%) " +
            "AND (:#{#teacherQueryDTO.sex} IS NULL OR t.sex = :#{#teacherQueryDTO.sex}) " +
            "AND (:#{#teacherQueryDTO.age} IS NULL OR t.age = :#{#teacherQueryDTO.age}) " +
            "AND (:#{#teacherQueryDTO.qualifications} IS NULL OR t.qualifications LIKE %:#{#teacherQueryDTO.qualifications}%)" +
            "AND (:#{#teacherQueryDTO.department} IS NULL OR d.department LIKE %:#{#teacherQueryDTO.department}%)")
    Page<TeacherPageVO> getTeacherPageByConditions(@Param("teacherQueryDTO") TeacherQueryDTO teacherQueryDTO, Pageable pageable);

    /**
     * 根据姓名模糊查询 (like)
     *
     * @param name
     * @return
     */
    List<Teacher> findByNameLike(String name);

    /**
     * 根据姓名和密码查询
     *
     * @param name
     * @param password
     * @return
     */
    List<Teacher> findByNameAndPassword(String name, String password);

    /**
     * 根据姓名模糊查询 (startingWith)
     *
     * @param name
     * @return
     */
    List<Teacher> findByNameStartingWith(String name);

    /**
     * 根据id集合删除教师信息
     *
     * @param ids
     */
    @Modifying
    @Query("DELETE FROM tb_teacher t WHERE t.id IN :ids")
    void deleteByIds(List<Integer> ids);

    /**
     * 根据教师工号查询教师信息
     *
     * @param tno
     * @return
     */
    Teacher findByTno(String tno);


    /**
     * 根据教师工号查询最大工号开头的教师集合
     *
     * @param maxTnoByYear
     * @return
     */
    List<Teacher> findByTnoStartingWith(String maxTnoByYear);
}
